package com.nbsaas.boot.wms.data.repository;

import com.nbsaas.boot.wms.data.entity.ProductAllocation;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 商品配货-数据处理功能
 */
@Repository
public interface ProductAllocationRepository extends JpaRepository<ProductAllocation, Long> {

    /**
     * 查询商品在指定店铺的配货情况
     * @param productId 商品ID
     * @param shopIds 店铺ID列表
     * @return 配货情况列表
     */
    @Query("SELECT pa FROM ProductAllocation pa WHERE pa.productId = :productId AND pa.shopId IN :shopIds")
    List<ProductAllocation> findByProductIdAndShopIds(@Param("productId") Long productId, @Param("shopIds") List<Long> shopIds);

    /**
     * 查询商品在指定店铺的配货数量（如果没有配货返回0）
     * @param productId 商品ID
     * @param shopId 店铺ID
     * @return 配货数量
     */
    @Query("SELECT COALESCE(SUM(pa.allocationQuantity), 0) FROM ProductAllocation pa WHERE pa.productId = :productId AND pa.shopId = :shopId")
    Integer getProductAllocationQuantityByShop(@Param("productId") Long productId, @Param("shopId") Long shopId);

    /**
     * 查询商品在多个店铺的配货情况，包括没有配货的店铺（返回0）
     * @param productId 商品ID
     * @param shopIds 店铺ID列表
     * @return 店铺配货情况映射
     */
    @Query("SELECT pa.shopId as shopId, COALESCE(SUM(pa.allocationQuantity), 0) as allocationQuantity " +
           "FROM ProductAllocation pa " +
           "WHERE pa.productId = :productId AND pa.shopId IN :shopIds " +
           "GROUP BY pa.shopId")
    List<Object[]> getProductAllocationByShops(@Param("productId") Long productId, @Param("shopIds") List<Long> shopIds);

    /**
     * 查询商品在指定店铺的可用配货数量
     * @param productId 商品ID
     * @param shopId 店铺ID
     * @return 可用配货数量
     */
    @Query("SELECT COALESCE(SUM(pa.availableQuantity), 0) FROM ProductAllocation pa WHERE pa.productId = :productId AND pa.shopId = :shopId AND pa.status = 'AVAILABLE'")
    Integer getAvailableAllocationQuantityByShop(@Param("productId") Long productId, @Param("shopId") Long shopId);

    /**
     * 查询商品在指定店铺的配货状态
     * @param productId 商品ID
     * @param shopId 店铺ID
     * @return 配货状态
     */
    @Query("SELECT pa.status FROM ProductAllocation pa WHERE pa.productId = :productId AND pa.shopId = :shopId ORDER BY pa.priority DESC LIMIT 1")
    String getProductAllocationStatusByShop(@Param("productId") Long productId, @Param("shopId") Long shopId);
} 